﻿'========================================================================
'功能:員工檔資料庫存取物件
'作者:andy
'日期: 2011/2/9
'========================================================================

Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Text
Imports System.Data

Imports MySql.Data.MySqlClient


Namespace OAMS.DAO
    Public Class UsersDAO
        Inherits BaseDAO

        Dim _dbName As String
        Public Sub New(ByVal dbName As String)
            _dbName = dbName
        End Sub

        ''' <summary>
        ''' 取得部門資料
        ''' </summary>
        ''' <param name="kind">0=正常,1=加一筆空白</param>
        ''' <returns>取得部門資料</returns>
        ''' <remarks>取得部門資料</remarks>
        Public Function GetDept(ByVal kind As String) As DataTable
            'Dim sb As New StringBuilder()
            'If kind = "1" Then
            '    sb.AppendLine(" select '' as DeptID,'' as DeptName union ")
            'End If
            'sb.AppendLine(" select rtrim([code] ) as DeptID ,rtrim([org] )as DeptName from dept_mapping_org")
            'Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)



            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                Dim sb As New StringBuilder()
                If kind = "1" Then
                    sb.AppendLine(" select '' as DeptID,'' as DeptName union ")
                End If
                con.Open()


                ' sb.AppendLine("Select * from oa_vip where emp_id=?emp_id AND Agent_emp_id=?Agent_emp_id  ")
                sb.AppendLine("select distinct   org_id as DeptID,org_id as DeptName  from `auoref`.`emp_data_all`  where ifnull(org_id,'')<>''  and active='Y'")
                cmd = New MySqlCommand(sb.ToString(), con)
              
                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                con.Close()
                Return oTable


            Catch ex As Exception
                con.Close()
                Throw (New Exception(ex.Message))
            Finally


            End Try
        End Function



        Public Function GetDeptByUID(ByVal UID As String) As DataTable
            Dim sb As New StringBuilder()
            Dim dept As String = GetUsers(UID).Rows(0)("Dept_ID").ToString
            'If dept.Length > 2 Then
            '    sb.AppendLine(" select distinct rtrim([org_id] ) as DeptID ,rtrim([org_id] )as DeptName from `auoref`.`emp_data_all`  where active='Y' and  org_id like '" + dept.Substring(0, 2) + "%'")
            'Else

            '    sb.AppendLine(" select distinct rtrim([org_id] ) as DeptID ,rtrim([org_id] )as DeptName from `auoref`.`emp_data_all`  where active='Y' and  org_id like '" + dept + "'")
            'End If

            'Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)

            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                
                con.Open()

                If dept.Length > 2 Then
                    sb.AppendLine(" select distinct rtrim([org_id] ) as DeptID ,rtrim([org_id] )as DeptName from `auoref`.`emp_data_all`  where active='Y' and  org_id like '" + dept.Substring(0, 2) + "%'")
                Else

                    sb.AppendLine(" select distinct rtrim([org_id] ) as DeptID ,rtrim([org_id] )as DeptName from `auoref`.`emp_data_all`  where active='Y' and  org_id like '" + dept + "'")
                End If
                cmd = New MySqlCommand(sb.ToString(), con)

                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                con.Close()
                Return oTable


            Catch ex As Exception
                con.Close()
                Throw (New Exception(ex.Message))
            Finally


            End Try
        End Function
        Public Function GetDeptName(ByVal DeptID As String) As String
            'Try
            '    Dim sb As New StringBuilder()

            '    sb.AppendLine(" select rtrim([code] ) as DeptID ,rtrim([org] )as DeptName from dept_mapping_org where code='" + DeptID + "'")
            '    Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0).Rows(0)("DeptName").ToString()
            'Catch ex As Exception
            '    Return ""
            'End Try
            Return DeptID


        End Function

        ''' <summary>
        ''' 檢查某一個工號是否在資料庫中
        ''' </summary>
        ''' <param name="UID"></param>
        ''' <returns>檢查某一個工號是否在資料庫中</returns>
        ''' <remarks>檢查某一個工號是否在資料庫中</remarks>
        Public Function UserIsExist(ByVal UID As String) As Boolean
            'Dim sqlParams As SqlParameter() = New SqlParameter(1) {}

            'sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            'sqlParams(0).Value = UID


            'Dim sb As New StringBuilder()

            'sb.AppendLine(" select * from Users where UserID=@UID")

            'Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0).Rows.Count > 0
            Dim sb As New StringBuilder()
          
            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try


                con.Open()


                sb.AppendLine(" select *  from `auoref`.`emp_data_all`  where emp_no=?UID")
              
                cmd = New MySqlCommand(sb.ToString(), con)
                cmd.Parameters.AddWithValue("?UID", UID)
                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                con.Close()
                Return oTable.Rows.Count > 0


            Catch ex As Exception
                con.Close()
                Throw (New Exception(ex.Message))
            Finally


            End Try





        End Function


        Public Function UserIsQuit(ByVal UID As String) As Boolean
            'Dim sqlParams As SqlParameter() = New SqlParameter(1) {}

            'sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            'sqlParams(0).Value = UID


            'Dim sb As New StringBuilder()

            'sb.AppendLine(" select * from Users where UserID=@UID")

            'Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0).Rows.Count > 0
            Dim sb As New StringBuilder()

            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try


                con.Open()


                sb.AppendLine(" select *  from `auoref`.`emp_data_all`  where emp_no=?UID  and ifNull(quit_date,now())<Now()")

                cmd = New MySqlCommand(sb.ToString(), con)
                cmd.Parameters.AddWithValue("?UID", UID)

                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                con.Close()
                Return oTable.Rows.Count > 0


            Catch ex As Exception
                con.Close()
                Throw (New Exception(ex.Message))
            Finally


            End Try





        End Function

        ''' <summary>
        ''' 查詢員工基本資料傳入工號或姓名都可以空白代表全部
        ''' </summary>
        ''' <param name="UIDOrUserName"></param>
        ''' <returns>查詢員工基本資料傳入工號或姓名都可以空白代表全部</returns>
        ''' <remarks>查詢員工基本資料傳入工號或姓名都可以空白代表全部</remarks>
        Public Function GetUsers(ByVal UIDOrUserName As String) As DataTable
            'Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            'sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            'sqlParams(0).Value = UIDOrUserName


            'Dim sb As New StringBuilder()

            'sb.AppendLine(" select * from Users where UserID like '%'+@UID+'%' or UserName like '%'+@UID+'%' ")

            'Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)

            Dim sb As New StringBuilder()

            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try


                con.Open()


                sb.AppendLine(" select emp_no as UserID ,emp_name as UserName ,e_mail as EMail,org_id as Dept_ID , site_name as Factory_ID ,LOGONID as login_id,come_date as TakeOfficeDate,birthday as Birth_day,ext_no as Ext_no,company")
                sb.AppendLine(" from `auoref`.`emp_data_all`  where emp_no like '%" + UIDOrUserName + "%' or emp_name like '%" + UIDOrUserName + "%'")

                cmd = New MySqlCommand(sb.ToString(), con)

                Dim oDbAdapter As MySqlDataAdapter

                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                con.Close()
                Return oTable
            Catch ex As Exception
                con.Close()
                Throw (New Exception(ex.Message))
            Finally


            End Try



        End Function


        ''' <summary>
        ''' 查出某一個人的基本資料
        ''' </summary>
        ''' <param name="login_id"></param>
        ''' <returns>查出某一個人的基本資料</returns>
        ''' <remarks>查出某一個人的基本資料</remarks>
        Public Function GetUser(ByVal login_id As String) As DataTable
            'Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            'sqlParams(0) = New SqlParameter("@login_id", SqlDbType.NVarChar, 100)
            'sqlParams(0).Value = login_id


            'Dim sb As New StringBuilder()

            'sb.AppendLine(" select A.* ,B.UICulture ,'' as dbName from Users A  where A.login_id = @login_id ")

            'Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)


            Dim sb As New StringBuilder()

            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try


                con.Open()


                sb.AppendLine(" select 'AUOOAMS' as dbName ,emp_no as UserID ,emp_name as UserName ,e_mail as EMail,org_id as Dept_ID , site_name as Factory_ID ,LOGONID as login_id,come_date as TakeOfficeDate,birthday as Birth_day,ext_no as Ext_no")
                sb.AppendLine(" from `auoref`.`emp_data_all`  where LOGONID=?login_id  and LOGONID!=''  and active='Y'  and (quit_date is null or quit_date>now()) ")

                cmd = New MySqlCommand(sb.ToString(), con)
                cmd.Parameters.AddWithValue("?login_id", login_id)

                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                con.Close()

                Return oTable
            Catch ex As Exception
                con.Close()
                Throw (New Exception(ex.Message))
            Finally

            End Try


        End Function

        ''' <summary>
        ''' 取得使用者姓名
        ''' </summary>
        ''' <param name="UID"></param>
        ''' <returns>取得使用者姓名</returns>
        ''' <remarks>取得使用者姓名</remarks>
        Public Function GetUserName(ByVal UID As String) As String
            'Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            'sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            'sqlParams(0).Value = UID


            'Dim sb As New StringBuilder()

            'sb.AppendLine(" select UserName from Users where UserID =@UID ")
            'Try
            '    Return SqlHelper.ExecuteScalar(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).ToString()
            'Catch ex As Exception
            '    Return ""
            'End Try
            Dim sb As New StringBuilder()

            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try


                con.Open()


                sb.AppendLine(" select emp_no as UserID ,emp_name as UserName ,e_mail as EMail,org_id as Dept_ID , site_name as Factory_ID ,LOGONID as login_id,come_date as TakeOfficeDate,birthday as Birth_day,ext_no as Ext_no")
                sb.AppendLine(" from `auoref`.`emp_data_all`  where emp_no=?UID")

                cmd = New MySqlCommand(sb.ToString(), con)
                cmd.Parameters.AddWithValue("?UID", UID)

                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                con.Close()
                If oTable.Rows.Count > 0 Then
                    Return oTable.Rows(0)("UserName").ToString()
                Else
                    Return ""
                End If

            Catch ex As Exception
                con.Close()
                Throw (New Exception(ex.Message))
            Finally


            End Try

        End Function

        '''' <summary>
        '''' 取得所有部門的資料
        '''' </summary>
        '''' <returns></returns>
        '''' <remarks></remarks>
        'Public Function GetDeptData() As DataTable


        '    Dim sb As New StringBuilder()

        '    sb.AppendLine("select DeptID,DeptName  from OA_Depart  ")

        '    Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)
        'End Function
        '''' <summary>
        '''' 取得所有部門的資料並加入1筆空白
        '''' </summary>
        '''' <returns></returns>
        '''' <remarks></remarks>
        'Public Function GetDeptDataAddBlank() As DataTable


        '    Dim sb As New StringBuilder()

        '    sb.AppendLine(" select '' as DeptID,'' as DeptName union select DeptID,DeptName  from OA_Depart  ")

        '    Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)
        'End Function

        ''' <summary>
        ''' 設定語言別到mySqlDB中
        ''' </summary>
        ''' <param name="UID">使用者代號</param>
        ''' <param name="UICulture">語言別</param>
        ''' <param name="dbName">資料庫代號</param>
        ''' <remarks> 設定語言別到mySqlDB中</remarks>

        Public Sub SetLanguage(ByVal UID As String, ByVal UICulture As String, ByVal dbName As String)
            Dim cmd As MySqlCommand
            Dim nameStr As String = ""
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try
                con.Open()
                cmd = New MySqlCommand("Delete from SC_UICulture where UID=?UID ", con)
                cmd.Parameters.AddWithValue("?UID", UID)
                cmd.ExecuteNonQuery()

                cmd = New MySqlCommand("insert into SC_UICulture(UID,UICulture)values(?UID,?UICulture)", con)
                cmd.Parameters.AddWithValue("?UID", UID)
                cmd.Parameters.AddWithValue("?UICulture", UICulture)
                cmd.ExecuteNonQuery()
                con.Close()

            Catch ex As Exception
                con.Close()
                Throw (New Exception(ex.Message))
            Finally

            End Try

        End Sub

        ''' <summary>
        ''' 取得語言別到mySqlDB中
        ''' </summary>
        ''' <param name="UID">使用者代號</param>
        ''' <param name="dbName">資料庫代號</param>
        '''<returns>取得語言別到mySqlDB中</returns>
        ''' <remarks> 取得語言別到mySqlDB中</remarks>
        Public Function GetLanguage(ByVal UID As String, ByVal dbName As String) As String
            Dim cmd As MySqlCommand
            Dim nameStr As String = ""
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try
                con.Open()
                cmd = New MySqlCommand("Select * from  SC_UICulture where UID=?UID  ", con)
                cmd.Parameters.AddWithValue("?UID", UID)
                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)

                If oTable.Rows.Count = 0 Then
                    Return "en-us"
                Else
                    Return oTable.Rows(0)("UICulture").ToString
                End If
                con.Close()
            Catch ex As Exception
                con.Close()
                Throw (New Exception(ex.Message))
            Finally

            End Try

            Return "en-us"



        End Function

        ''' <summary>
        ''' 取得某一頁面的權限
        ''' </summary>
        ''' <param name="URL">URL</param>
        ''' <param name="QueryString">QueryString</param>
        ''' <param name="UserID"></param>
        ''' <returns>取得某一頁面的權限</returns>
        ''' <remarks>取得某一頁面的權限</remarks>
        Public Function GetURLRights(ByVal URL As String, ByVal QueryString As String, ByVal UserID As String) As DataTable
            Dim sb As New StringBuilder()
            sb.AppendLine("  select distinct A.ProgramID,B.SeqNo,B.DisplayName,A.Name,A.URL ,C.UseRight,A.isMenu ")
            sb.AppendLine(" from Menu A")
            sb.AppendLine(" left join MenuRights B on A.ProgramID=B.ProgramID")
            sb.AppendLine(" left join GroupRights C on C.ProgramID=B.ProgramID and  C.SeqNo=B.SeqNo")
            sb.AppendLine(" left join GroupMember D on C.GroupID=D.GroupID ")
            sb.AppendLine(" where ((A.URL='" & URL & "' and A.QueryString=0) or (A.QueryString=1 and A.URL='" & QueryString & "')) ")
            sb.AppendLine("and ( (A.NeedControl=0)   or   ( D.UID='" & UserID & "'  and C.UseRight=1) ) ")

            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()
                cmd = New MySqlCommand(sb.ToString(), con)

                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                con.Close()
                Return oTable
            Catch ex As Exception
                con.Close()
                Throw (New Exception(ex.Message))
            Finally

            End Try

        End Function

        ''' <summary>
        ''' 取得第1層的Menu
        ''' </summary>
        ''' <param name="UserID">員工代號</param>
        ''' <returns>取得第1層的Menu</returns>
        ''' <remarks>取得第1層的Menu</remarks>
        Public Function GetRootMenu(ByVal UserID As String) As DataTable
            Dim sb As New StringBuilder()
            sb.AppendLine(" select distinct A.ProgramID,A.URL ,A.RootName ,A.Name  ,A.ChnName,A.EngName ,A.OrderByID  from Menu A   left join MenuRights B on A.ProgramID=B.ProgramID   left join GroupRights C on C.ProgramID=B.ProgramID and  C.SeqNo=B.SeqNo  ")
            sb.AppendLine("  left join GroupMember D on C.GroupID=D.GroupID  where ( (A.NeedControl=0)   or   ( D.UID='" & UserID & "'  and C.UseRight=1) ) and A.RootName=''  and A.IsMenu=1 order by OrderByID,ProgramID")
           
            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()
                cmd = New MySqlCommand(sb.ToString(), con)

                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                con.Close()
                Return oTable
            Catch ex As Exception
                con.Close()
                Throw (New Exception(ex.Message))
            Finally


            End Try

        End Function

        ''' <summary>
        ''' 取得第2層的Menu
        ''' </summary>
        ''' <param name="RootName">第1層的名稱</param>
        ''' <param name="UID">員工代號</param>
        ''' <returns>取得第2層的Menu</returns>
        ''' <remarks>取得第2層的Menu</remarks>
        Public Function GetSubMenu(ByVal RootName As String, ByVal UID As String) As DataTable
            Dim sb As New StringBuilder()
            sb.AppendLine(" select distinct A.ProgramID,A.URL ,A.RootName ,A.Name ,A.ChnName,A.EngName  from Menu A   left join MenuRights B on A.ProgramID=B.ProgramID   left join GroupRights C on C.ProgramID=B.ProgramID and  C.SeqNo=B.SeqNo ")
            sb.AppendLine(" left join GroupMember D on C.GroupID=D.GroupID  where ( (A.NeedControl=0)   or   ( D.UID='" & UID & "'  and C.UseRight=1) ) and A.IsMenu =1  and A.Name<>'" & RootName & "' and  A.RootName='" & RootName & "'")

            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()
                cmd = New MySqlCommand(sb.ToString(), con)

                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                con.Close()
                Return oTable
            Catch ex As Exception
                con.Close()
                Throw (New Exception(ex.Message))
            Finally
            End Try

        End Function

        ''' <summary>
        ''' 檢查有代理人設定,true=有代理人
        ''' </summary>
        ''' <param name="emp_id">VIP</param>
        ''' <param name="Agent_emp_id">代理人</param>
        ''' <returns>檢查有代理人設定,true=有代理人</returns>
        ''' <remarks>檢查有代理人設定,true=有代理人</remarks>
        Public Function ChkVIPAgent(ByVal emp_id As String, ByVal Agent_emp_id As String) As Boolean
            Dim cmd As MySqlCommand
            Dim nameStr As String = ""
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try
                con.Open()
                cmd = New MySqlCommand("Select * from  oa_vip where emp_id=?emp_id and Agent_emp_id  ", con)
                cmd.Parameters.AddWithValue("?emp_id", emp_id)
                cmd.Parameters.AddWithValue("?Agent_emp_id", Agent_emp_id)
                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)

                If oTable.Rows.Count > 0 Then
                    Return True
                End If
            Catch ex As Exception
            Finally
                con.Close()
            End Try ''檢查個人代理人
            ''檢查有無在個人代理人設定
            Try
                con.Open()
                cmd = New MySqlCommand("Select * from  oa_vip where emp_id=?emp_id", con)
                cmd.Parameters.AddWithValue("?emp_id", emp_id)

                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)

                If oTable.Rows.Count = 0 Then ''沒有設定值
                    Return False
                End If
            Catch ex As Exception
            Finally
                con.Close()
            End Try ''檢查個人代理人

            ''再檢查部門代理人
            Dim dept_id As String
            Dim ii As Integer
            Dim uDao As OAMS.DAO.UsersDAO = New OAMS.DAO.UsersDAO(_dbName)
            dept_id = uDao.GetUser(emp_id).Rows(0)("Dept_ID").ToString
            For ii = dept_id.Length To 1 Step -1

                Try
                    con.Open()
                    cmd = New MySqlCommand("Select * from  oa_vipdept where emp_id=?emp_id and Dept_ID=?Dept_ID  ", con)
                    cmd.Parameters.AddWithValue("?emp_id", Agent_emp_id)
                    cmd.Parameters.AddWithValue("?Dept_ID", dept_id.Substring(0, ii))
                    Dim oDbAdapter As MySqlDataAdapter
                    oDbAdapter = New MySqlDataAdapter(cmd)
                    Dim oTable As Data.DataTable
                    oTable = New Data.DataTable()
                    oDbAdapter.Fill(oTable)
                    If oTable.Rows.Count > 0 Then
                        Return True
                    End If
                    con.Close()

                Catch ex As Exception
                    con.Close()
                    Throw (New Exception(ex.Message))
                Finally

                End Try
            Next
            Return False



        End Function



        ''' <summary>
        ''' 取得代理人,如有個人代理設定以個人為準,若不在個人設定的會找小單位找不到再找大單直到全找完為止
        ''' </summary>
        ''' <param name="emp_id">VIP</param>
        ''' <returns>取得代理人,如有個人代理設定以個人為準,若不在個人設定的會找小單位找不到再找大單直到全找完為止</returns>
        ''' <remarks>取得代理人,如有個人代理設定以個人為準,若不在個人設定的會找小單位找不到再找大單直到全找完為止</remarks>
        Public Function GetVIPAgent(ByVal emp_id As String) As String
            Dim cmd As MySqlCommand
            Dim nameStr As String = ""
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try
                con.Open()
                cmd = New MySqlCommand("Select * from  oa_vip where emp_id=?emp_id   ", con)
                cmd.Parameters.AddWithValue("?emp_id", emp_id)

                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)

                If oTable.Rows.Count > 0 Then
                    If oTable.Rows(0)("Agent_emp_id").ToString.Trim <> "" Then
                        Return oTable.Rows(0)("Agent_emp_id").ToString
                    End If
                Else '沒有設定值直接傳回空字串
                    Return ""
                End If
            Catch ex As Exception
            Finally
                con.Close()
            End Try ''找個人代理人
           


            ''再找部門代理人
            Dim dept_id As String
            Dim ii As Integer
            Dim uDao As OAMS.DAO.UsersDAO = New OAMS.DAO.UsersDAO(_dbName)
            dept_id = uDao.GetUsers(emp_id).Rows(0)("Dept_ID").ToString
            For ii = dept_id.Length To 1 Step -1

                Try
                    con.Open()
                    cmd = New MySqlCommand("Select * from  oa_vipdept where Dept_ID=?Dept_ID  ", con)

                    cmd.Parameters.AddWithValue("?Dept_ID", dept_id.Substring(0, ii))
                    Dim oDbAdapter As MySqlDataAdapter
                    oDbAdapter = New MySqlDataAdapter(cmd)
                    Dim oTable As Data.DataTable
                    oTable = New Data.DataTable()
                    oDbAdapter.Fill(oTable)
                    If oTable.Rows.Count > 0 Then
                        Return oTable.Rows(0)("emp_id").ToString()
                    End If
                    con.Close()

                Catch ex As Exception
                    con.Close()
                    Throw (New Exception(ex.Message))
                Finally


                End Try
            Next
            Return ""
        End Function

        Public Function GetAUOFORM87_CODELIST(ByVal item As String, ByVal kind As String) As DataTable
            '1.除帳.  2.繳回
            Dim sb As New StringBuilder()
            Dim sqlParams As SqlParameter() = New SqlParameter(0) {}


            sb.AppendLine("select * from   AUOFORM87_CODELIST where item=@item")
            If kind = "1" Then
                sb.AppendLine(" and approverScrap<>'' ")
            Else
                sb.AppendLine(" and approveroa<>'' ")
            End If


            sqlParams(0) = New SqlParameter("@item", SqlDbType.NVarChar)
            sqlParams(0).Value = item
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function
        Public Function GetEmpData(ByVal emp_no As String) As DataTable
            'Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            'sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            'sqlParams(0).Value = UIDOrUserName


            'Dim sb As New StringBuilder()

            'sb.AppendLine(" select * from Users where UserID like '%'+@UID+'%' or UserName like '%'+@UID+'%' ")

            'Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)

            Dim sb As New StringBuilder()

            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try


                con.Open()


                sb.AppendLine(" select emp_no as UserID ,emp_name as UserName ,e_mail as EMail,org_id as Dept_ID , site_name as Factory_ID ,LOGONID as login_id,come_date as TakeOfficeDate,birthday as Birth_day,ext_no as Ext_no,company, quit_date")
                sb.AppendLine(" from `auoref`.`emp_data_all`  where emp_no = '" + emp_no + "'")

                cmd = New MySqlCommand(sb.ToString(), con)

                Dim oDbAdapter As MySqlDataAdapter

                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                con.Close()

                Return oTable
            Catch ex As Exception
                con.Close()
                Throw (New Exception(ex.Message))
            Finally


            End Try



        End Function
        Public Sub InsertHEChgData(ByVal form_no As Integer, ByVal emp_no As String)
            'Dim sb As New StringBuilder()
            'Dim sqlParams As SqlParameter() = New SqlParameter(2) {}

            'sqlParams(0) = New SqlParameter("@form_no", SqlDbType.Int, form_no)
            'sqlParams(0).Value = form_no
            'sqlParams(1) = New SqlParameter("@emp_no", SqlDbType.NVarChar)
            'sqlParams(1).Value = emp_no
            'sb.AppendLine("insert into HRChgData(form_no,emp_no) values (@form_no,@emp_no)")

            'SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)


            Dim sb As New StringBuilder()
            sb.AppendLine("insert into HRChgData (form_no,emp_no) values (?form_no,?emp_no)")
            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try
                con.Open()
                cmd = New MySqlCommand(sb.ToString, con)
                cmd.Parameters.AddWithValue("?form_no", form_no)
                cmd.Parameters.AddWithValue("?emp_no", emp_no)

                cmd.ExecuteNonQuery()
                con.Close()
            Catch ex As Exception
                con.Close()
                Throw (New Exception(ex.Message))
            Finally

            End Try



        End Sub


        Public Function ChkHRChgIsExist(ByVal form_no As Integer) As Boolean


            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()

                Dim sb As New StringBuilder()
                sb.AppendLine("select * from HRChgData  where form_no=?form_no ")
                cmd = New MySqlCommand(sb.ToString(), con)
                cmd.Parameters.AddWithValue("?form_no", form_no)

                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As DataTable
                oTable = New DataTable()
                oDbAdapter.Fill(oTable)
                Return oTable.Rows.Count > 0
                con.Close()
            Catch ex As Exception
                con.Close()
                Throw (New Exception(ex.Message))
            Finally


            End Try
        End Function

        Public Function GetHROrgChgData() As DataTable
            Dim sb As New StringBuilder()

            'sb.AppendLine("select * from au3hrts1.e_offering.dbo.vw_hrorgchange  ")
            sb.AppendLine("select * from vw_hrorgchange  where go_date>=getDate()   ")

            Dim dt As DataTable = SqlHelper.ExecuteDataset(HRConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)

            Dim dt2 As DataTable = New DataTable()
            dt2.Columns.Add("form_no")
            dt2.Columns.Add("emp_no")
            Dim dr2 As DataRow
            For Each dr As DataRow In dt.Rows

                If Not ChkHRChgIsExist(Integer.Parse(dr("form_no").ToString)) Then
                    dr2 = dt2.NewRow
                    dr2("form_no") = dr("form_no").ToString
                    dr2("emp_no") = dr("emp_no").ToString
                    dt2.Rows.Add(dr2)
                End If


            Next


            Return dt2

        End Function
        Public Function GetBirthdayData() As DataTable
            Dim sb As New StringBuilder()

            'sb.AppendLine("select * from au3hrts1.e_offering.dbo.vw_hrorgchange  ")
            sb.AppendLine("select * from AUOREF.dbo.emp_data_all where month(birthday)=month(getdate()) and day(birthday)=day(getdate())  and active='Y'  ")

            Dim dt As DataTable = SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)
            Return dt

        End Function

    End Class




End Namespace


